package org.jings.simplemanager.activity;

import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;

import org.jings.simplemanager.R;
import org.jings.simplemanager.sqlite.DbManager;
import org.jings.simplemanager.sqlite.MySqliteHelper;

/**
 * Created by JOY on 2016/11/20.
 */

public class SQLiteActivity extends Activity {

    private MySqliteHelper helper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.sqlite_layout);
        helper = DbManager.getHelper(this);

    }

    public void createDb(SQLiteDatabase db){
        String sql = "create table people (id Integer primary key,name varchar(10))";
        try {
            db.execSQL(sql);
            Toast.makeText(this, "create people success", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void dropDb(SQLiteDatabase db){
        String sql = "drop table people";
        try {
            db.execSQL(sql);
            Toast.makeText(this, "drop people success", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void insert(SQLiteDatabase db){
        String sql = "insert into people values(1,'zhangsan')";
        try {
            db.execSQL(sql);
            Toast.makeText(this, "insert into people success", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void update(SQLiteDatabase db) {
        String time = String.valueOf(System.currentTimeMillis());
        String num = time.substring(time.length() - 1);
        String sql = "update people set name = 'lisi" + num + "' where id = 1";
        try {
            db.execSQL(sql);
            Toast.makeText(this, "update people success", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void delete(SQLiteDatabase db) {
        String sql = "delete from people";
        try {
            db.execSQL(sql);
            Toast.makeText(this, "delete from people success", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void select(SQLiteDatabase db) {
        String sql = "select * from people";
        StringBuilder sb = new StringBuilder();
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                sb.append(cursor.getInt(0));
                sb.append(cursor.getString(1));
                sb.append("\n");
            }
            Toast.makeText(this, sb.toString(), Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();
        }
    }

    public void onClick(View view){
        SQLiteDatabase db = helper.getWritableDatabase();
        switch (view.getId()) {
            case R.id.create_table:
                this.createDb(db);
                break;
            case R.id.drop_table:
                this.dropDb(db);
                break;
            case R.id.insert:
                this.insert(db);
                break;
            case R.id.update:
                this.update(db);
                break;
            case R.id.delete:
                this.delete(db);
                break;
            case R.id.select:
                this.select(db);
                break;
        }
        db.close();
//        Toast.makeText(this, "view " + view.getId() + " be clicked!", Toast.LENGTH_SHORT).show();
    }
}
